﻿using Dapper;
using Dapper.Contrib.Extensions;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tauren.Form.Entity.Global;
using Tauren.Form.Entity.Model;
using Tauren.Form.Entity.Output;
using Tauren.Form.Infrastructure.Globals;

namespace Tauren.Form.DLL.Field_FormDLL
{
    /// <summary>
    /// 设置表单需要的表信息
    /// </summary>
    internal class Field_FormBusiness : Base.BaseDLL, IField_FormBusiness
    {
        public Field_FormBusiness(IConnectionDLLBase _dLLBase) : base(_dLLBase)
        {
        }



        public double GetSeq()
        {
            Field_Form TableInfo = this.BusinessConnection.QueryFirstOrDefault<Field_Form>("SELECT * FROM Field_Form WHERE havedelete=0  ORDER BY autoid DESC LIMIT 0,1");
            if (TableInfo is null) return 1;
            else return (TableInfo.Sequence <= 0 ? 1 : TableInfo.Sequence + 1);
        }

        /// <summary>
        /// 分页获取列表数据
        /// </summary>
        /// <param name="queryModel"></param>
        /// <param name="Total"></param>
        /// <returns></returns>
        public List<PageField_FormTable> Pages(QueryModel queryModel, out long Total)
        {
            Total = 0;
            string where = "";
            if (!string.IsNullOrEmpty(queryModel.KeyWord))
            {
                where += $"   AND r.cnname LIKE '%{queryModel.KeyWord}%' or r.enname LIKE '%{queryModel.KeyWord}%'";
            }
            if (string.IsNullOrEmpty(queryModel.SortField))
            {
                queryModel.SortField = "sequence";
                queryModel.IsAsc = false;
            }
            else
            {
                queryModel.SortField = queryModel.SortField.ToLower().Replace("dic_", "").Replace("dic", "").Trim();
            }
            if (queryModel.ExceptDataField.Any())
            {
                queryModel.ExceptDataField.ForEach(o =>
                {
                    if (!string.IsNullOrEmpty(o.Value + ""))
                        where += $" AND r.{o.Label}!='{o.Value}'";
                });
            } 
            if (queryModel.paramters.Any())
            {
                queryModel.paramters.ForEach(p =>
                {
                    where += $"  and r.{p.Label} like '%{p.Value}%'  ";
                });
            }
            if (!queryModel.UseDisabledData)
            {
                where += $" AND r.status=3  ";
            }
            string asc = queryModel.IsAsc ? "ASC" : "DESC";
            queryModel.Page = queryModel.Page == 0 ? 1 : queryModel.Page;
            StringBuilder sql = new StringBuilder();
            sql.Append(@$"SELECT count(r.autoid) as cnt FROM Field_Form r WHERE r.havedelete !=1
                        {where}  ;");
            sql.Append(@$"SELECT r.autoid, r.currentversion, r.cnname, r.enname, r.havedelete, r.`sequence`, r.creator, r.modifier, r.creatdate, r.modifdate,r.status,ffv.autoid currentversionid FROM Field_Form r left join field_form_version ffv on r.currentversion =ffv.versionno and ffv .formid =r.autoid
                          WHERE r.havedelete !=1
                        {where}  ORDER BY {queryModel.SortField} {asc} LIMIT {(queryModel.Page - 1) * queryModel.PageSize},{ queryModel.PageSize} ;");
            using (var reader = BusinessConnection.QueryMultiple(sql.ToString()))
            {
                Total = reader.ReadFirstOrDefault<int>();
                List<PageField_FormTable> pageModules = reader.Read<PageField_FormTable>().ToList();
                long RowNo = ((queryModel.Page == 0 ? 1 : queryModel.Page) - 1) * queryModel.PageSize + 1;
                pageModules.ForEach(o =>
                {
                    o.RowNo = RowNo;
                    RowNo++;
                });
                return pageModules;
            }
        }

        public List<Form_Field_Primary_TableInfo_OutPut> GetTables(long ParentId) => BusinessConnection.Query<Form_Field_Primary_TableInfo_OutPut>("SELECT * FROM form_field_primary_tableinfo WHERE formid=@FormId", new Entity.Model.Form_Field_Primary_TableInfo { FormId = ParentId }).AsList();


        public List<Form_Field_Child_TableInfo_OutPut> GetChilds(long ParentId) => BusinessConnection.Query<Form_Field_Child_TableInfo_OutPut>("SELECT * FROM form_field_child_tableinfo WHERE primarytableid=@PrimaryTableId", new Entity.Model.Form_Field_Child_TableInfo { PrimaryTableId = ParentId }).AsList();

        /// <summary>
        /// 判断表单名称是否重复
        /// </summary>
        /// <param name="autoId"></param>
        /// <param name="CNName"></param>
        /// <param name="ENName"></param>
        /// <returns></returns>
        public bool IstTableRepeat(long autoId, string CNName, string ENName)
        {
            string sql = $"SELECT * FROM Field_Form WHERE (enname='{CNName}' or enname='{ENName}')";
            if (autoId != 0)
            {
                sql = $"{sql} AND autoid != {autoId}";
            }
            return BusinessConnection.Query<Entity.Model.Field_Form>(sql).Any();
        }

        public bool Add(List<Form_Field_Child_TableInfo> Childs)
        {
            bool b = true;
            if (Childs != null && Childs.Any())
            {
                b = BusinessConnection.Insert(Childs) >= 0;
            }
            return b;
        }

        public bool Add(List<Form_Field_Primary_TableInfo> tableInfos, long id)
        {
            bool b = true;
            BusinessConnection.Execute("DELETE FROM form_field_primary_tableinfo WHERE formid=@FormId", new Entity.Model.Form_Field_Primary_TableInfo { FormId = id });
            if (tableInfos != null && tableInfos.Any())
            {
                b = BusinessConnection.Insert(tableInfos) >= 0;
            }
            return b;
        }

        public List<Form_Field_TableInfo_OutPut> GetFormFieldTableInfos(long FormId) => BusinessConnection.Query<Form_Field_TableInfo_OutPut>(@"select
	                                                                                                                                                ffpt.formid,
	                                                                                                                                                ffpt.tablecode,
	                                                                                                                                                ffpt.foreignkey `primarykey`,
	                                                                                                                                                ffct.tablecode childtablecode,
	                                                                                                                                                ffct.foreignkey,
                                                                                                                                                    ffpt.tableid
                                                                                                                                                from
	                                                                                                                                                form_field_primary_tableinfo ffpt
                                                                                                                                                left join form_field_child_tableinfo ffct on
	                                                                                                                                                ffpt .autoid = ffct.primarytableid WHERE ffpt.formid=@FormId", new Entity.Model.Form_Field_Primary_TableInfo { FormId = FormId }).AsList();

        public bool DeleteTableInfos(long Ids)
        {
            return BusinessConnection.Execute($"DELETE FROM form_field_child_tableinfo WHERE primarytableid in (select autoid FROM form_field_primary_tableinfo WHERE formid =@FormId);DELETE FROM form_field_primary_tableinfo WHERE formid =@FormId", new Entity.Model.Form_Field_Primary_TableInfo { FormId = Ids }) >= 0;
        }

        public List<Field_Form_VersionExt> GetVersionFields(long FormId, long VersionId)
        {
            StringBuilder stringBuilder = new StringBuilder();
            string where = "";
            if (VersionId > 0)
            {
                stringBuilder.Append("field_form_version");
                where = $"and b.autoid={VersionId}";
            }
            else
            {
                stringBuilder.Append($@" ( SELECT * FROM field_form_version ffv  WHERE ffv.formid = @FormId ORDER BY ffv.creatdate DESC  LIMIT 0, 1 )");
            }
            return BusinessConnection.Query<Field_Form_VersionExt>($@"SELECT a.cnname formcnname,a.enname formenname,
                                                                        a.useapplicantempid, b.autoid versionid, b.versionno, a.autoid AS formid, c.autoid AS areaid
	                                                                    , c.cnname AS areacnname, c.enname AS areaenname, c.areacode, c.tablecode, c.presentation
	                                                                    , c.rowes, c.columnes, c.records, c.`sequence` AS areaseq, d.autoid AS fieldid
	                                                                    , d.fieldcode, d.fieldkey, d.cnname, d.enname, d.controlstype
	                                                                    , d.controlsouces, d.`type`, d.defaultvalue, d.required, d.max
	                                                                    , d.min, d.precisions, d.selecttype, d.`sequence`, d.`show`
	                                                                    , d.editor, e.leftbracket, e.cascadefield, e.`condition`, e.cascadefieldvalue
	                                                                    , e.rightbracket, e.associated, f.regulars,c.maintable,d.flength,1 areausable,1 fieldusable
                                                                    FROM field_form a
	                                                                    LEFT JOIN  {stringBuilder.ToString()}  b
	                                                                    ON a.autoid = b.formid
	                                                                    LEFT JOIN field_form_area c ON c.versionid = b.autoid
	                                                                    LEFT JOIN field_form_area_fields d ON d.areaid = c.autoid
	                                                                    LEFT JOIN field_form_area_fields_condition e ON d.fieldkey = e.fieldkey
	                                                                    LEFT JOIN field_form_area_fields_regular f ON d.autoid = f.fieldId 
                                                                    WHERE a.autoid = @FormId { where} ", new Entity.Model.Form_Field_Primary_TableInfo { FormId = FormId }).AsList();
        }

        public List<TableFields> GetTableFields(string TableCode) => BasicDataConnection.Query<TableFields>($@"select
	                                                                                                                t.tablecode,
	                                                                                                                t.enable as TableEnable,
                                                                                                                    t.records,
                                                                                                                    t.sequence,
	                                                                                                                t.havedelete TableHaveDelete,
                                                                                                                    t.cnname tablename,
                                                                                                                    t.enname tableenname,
	                                                                                                                f.fieldcode,
	                                                                                                                f.enable as FieldEnable,
                                                                                                                    f.sequence seq,
                                                                                                                    f.fieldenname,
                                                                                                                    f.fieldname,
	                                                                                                                f.havedelete FieldHaveDelete 
                                                                                                            from tableinfo t
                                                                                                            left join fields f on
	                                                                                                            f.tablecode = t.tablecode
                                                                                                            where
	                                                                                                            t.tablecode in({TableCode})").AsList();


        /// <summary>
        /// 防止数据库添加重复数据
        /// </summary>
        /// <param name="Model">表单数据</param>
        /// <returns></returns>
        public long UnRepeatAdd(Field_Form Model)
        {
            StringBuilder sb = new StringBuilder(@"INSERT INTO field_form (autoid, cnname, enname, currentversion, useapplicantempid
	                                                                        , havedelete, status, `sequence`, creator, modifier
	                                                                        , creatdate, modifdate)
                                                    SELECT @AutoId, @CNName, @ENName, @CurrentVersion, @UseApplicantEmpId,@HaveDelete,@Status
	                                                    ,  (select ifnull(max(`sequence`),0)+1 from field_form), @Creator, @Modifier, now(), now()
                                                    FROM DUAL
                                                    WHERE NOT EXISTS (
	                                                    SELECT *
	                                                    FROM field_form
	                                                    WHERE cnname =@CNName
		                                                    AND enname =@ENName
                                                    );");
            return this.BusinessConnection.Execute(sb.ToString(), Model);
        }

        public List<TableFields> GetTableInfos(string TableCode) => BasicDataConnection.Query<TableFields>($@"select
	                                                                                                            t.tablecode,
	                                                                                                            t.enable as TableEnable,
                                                                                                                t.records,
                                                                                                                t.sequence,
	                                                                                                            t.havedelete TableHaveDelete,
                                                                                                                t.cnname tablename,
                                                                                                                t.enname tableenname  from tableinfo t 
                                                                                                            where
	                                                                                                            t.tablecode in({TableCode})").AsList();

    }
}
